﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using System.Reflection;
using System.Web;

namespace Com.CMS263.Util
{
    public static class ExcelUtil
    {
        /// <summary>
        /// 查询EXCEL电子表格添加到DATASET
        /// </summary>
        /// <param name="filenameurl">文件路径</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public static DataSet ExecleDs(string filenameurl, string table,string name)
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filenameurl + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"$]", conn);
            odda.Fill(ds, table);
            return ds;
        }
        /**/
        /// <summary>
        /// 将一组对象导出成EXCEL      这是对外方法     在外面调用这个方法就行    得导入命名空间
        /// </summary>
        /// <typeparam name="T">要导出对象的类型</typeparam>
        /// <param name="objList">一组对象</param>
        /// <param name="FileName">导出后的文件名</param>
        /// <param name="columnInfo">列名信息</param>
        public static void ExExcel<T>(List<T> objList, string FileName, Dictionary<string, string> columnInfo)
        {
            ExExcel(objList, FileName, columnInfo, null);

        }
        /**/
        /// <summary>
        /// 将一组对象导出成EXCEL
        /// </summary>
        /// <typeparam name="T">要导出对象的类型</typeparam>
        /// <param name="objList">一组对象</param>
        /// <param name="FileName">导出后的文件名</param>
        /// <param name="columnInfo">列名信息</param>
        /// <param name="other">追加其他内容</param>
        public static void ExExcel<T>(List<T> objList, string FileName, Dictionary<string, string> columnInfo, string other)
        {
            if (columnInfo.Count == 0) { return; }
            if (objList.Count == 0) { return; }
            //生成EXCEL的HTML
            string excelStr = "";

            Type myType = objList[0].GetType();
            //根据反射从传递进来的属性名信息得到要显示的属性
            List<PropertyInfo> myPro = new List<PropertyInfo>();
            foreach (string cName in columnInfo.Keys)
            {
                PropertyInfo p = myType.GetProperty(cName);
                if (p != null)
                {
                    myPro.Add(p);
                    excelStr += columnInfo[cName] + "\t";
                }
            }
            //如果没有找到可用的属性则结束
            if (myPro.Count == 0) { return; }
            excelStr += "\n";

            foreach (T obj in objList)
            {
                foreach (PropertyInfo p in myPro)
                {
                    if (p.Name == "addMoney")
                    {
                        excelStr += (Convert.ToDouble(p.GetValue(obj, null))*100).ToString("0.00")+"%" + "\t";
                    }
                    else{
                    excelStr += p.GetValue(obj, null) + "\t";
                    }
                }
                excelStr += "\n";
            }
            if (!string.IsNullOrEmpty(other))
            {
                excelStr += other;
            }
            //输出EXCEL
            HttpResponse rs = System.Web.HttpContext.Current.Response;
            rs.Clear();
            rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            rs.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, Encoding.UTF8));
            rs.ContentType = "application/ms-excel";
            rs.Write(excelStr);
            rs.End();
        }
        //protected void Button1_Click(object sender, EventArgs e)
        //{
        //    if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        //    {
        //        Response.Write("<script>alert('请您选择Excel文件')</script> ");
        //        return;//当无文件时,返回
        //    }
        //    string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        //    if (IsXls != ".xls")
        //    {
        //        Response.Write("<script>alert('只可以选择Excel文件')</script>");
        //        return;//当选择的不是Excel文件时,返回
        //    }
        //    SqlConnection cn = new SqlConnection(strConn);
        //    cn.Open();
        //    string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        //    string savePath = Server.MapPath(("~/upfiles/") + filename);//Server.MapPath 获得虚拟服务器相对路径
        //    FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        //    DataSet ds = ExecleDs(savePath, filename);           //调用自定义方法
        //    DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        //    int rowsnum = ds.Tables[0].Rows.Count;
        //    if (rowsnum == 0)
        //    {
        //        Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        //    }
        //    else
        //    {
        //        for (int i = 0; i < dr.Length; i++)
        //        {
        //            string cpxh = dr[i]["产品型号"].ToString();//日期 excel列名【名称不能变,否则就会出错】
        //            string cssm = dr[i]["参数说明"].ToString();//编号 列名 以下类似
        //            string fzxs = dr[i]["封装形式"].ToString();
        //            string type = dr[i]["类别"].ToString();

        //            string sqlcheck = "select count(*) from products where cpxh='" + cpxh + "'And cssm='" + cssm + "' and fzxs='" + fzxs + "'";  //检查用户是否存在
        //            SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
        //            int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
        //            if (count < 1)
        //            {
        //                string insertstr = "insert into products (cpxh,cssm,fzxs,pdf,type) values('" + cpxh + "','" + cssm + "','" + fzxs + "',''," + type + ")";

        //                SqlCommand cmd = new SqlCommand(insertstr, cn);
        //                try
        //                {
        //                    cmd.ExecuteNonQuery();
        //                }
        //                catch (MembershipCreateUserException ex)       //捕捉异常
        //                {
        //                    Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
        //                }
        //            }
        //            else
        //            {
        //                Response.Write("<script>alert('内容重复的已经被过滤！');location='default.aspx'</script></script> ");
        //                continue;
        //            }
        //        }
        //        Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
        //    }

        //    cn.Close();

        //}
    }
}
